package lib.db.clip;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import lib.db.pool.MySQLConnection;

import inet.util.Logger;
import inet.util.UTF8Tool;

public class ClipDAO {
	private Logger logger = null;
	private MySQLConnection dbPool = null;
	public ClipDAO(){
		logger =new Logger(this.getClass().getName());
		try {
			dbPool = new MySQLConnection("private_db");
		} catch (Exception e) {
		}
	}
	
	public boolean insertRow(String name, BigDecimal cateId, int status, String clipLength, 
			int clipCapacity, String fileContent, String fileImage) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		boolean result = false;
		try {
			strSQL = " INSERT INTO clip( name, name_vn, cate_id, status, clip_length, clip_capacity, content, image, gen_date, last_updated) " +
					 " VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW()) ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setString(1, name);
			psmt.setString(2, UTF8Tool.coDau2KoDau(name));
			psmt.setBigDecimal(3, cateId);
			psmt.setInt(4, status);
			psmt.setString(5, clipLength);
			psmt.setInt(6, clipCapacity);
			psmt.setString(7, fileContent);
			psmt.setString(8, fileImage);
			
			if(psmt.executeUpdate() > 0){
				result = true;
			}
		
		} catch (Exception e) {
			logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt );
		}
		return result;
	}
	
	public Clip getRow(BigDecimal id) {
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		Clip clip = null;
		
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT id, name, name_vn, clip_length, clip_capacity, cate_id, content, image, status, gen_date, last_updated, download_counter" +
					 " FROM clip WHERE id = ? ";
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, id);
			rs = psmt.executeQuery();
			if (rs.next()) {
				clip = new Clip();
				clip.setId(rs.getBigDecimal(1));
				clip.setName(rs.getString(2));
				clip.setNameVn(rs.getString(3));
				clip.setClipLength(rs.getString(4));
				clip.setClipCapacity(rs.getInt(5));
				clip.setCateId(rs.getBigDecimal(6));
				clip.setUrlContent(rs.getString(7));
				clip.setUrlImage(rs.getString(8));
				clip.setStatus(rs.getInt(9));
				clip.setGenDate(rs.getTimestamp(10));
				clip.setLastUpdated(rs.getTimestamp(11));
				clip.setDownloadCounter(rs.getInt(12));
			}
		} catch (Exception e) {
			logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return clip;
	}
	
	public int countAll() {
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		int result = 0;
		
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT COUNT(id) FROM clip ";
			psmt = conn.prepareStatement(strSQL);
			rs = psmt.executeQuery();
			
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception e) {
			logger.error("countAll: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return result;
	}
	
	public int countByCateId( BigDecimal cateId) {
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		int result = 0;
		
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT COUNT(id) FROM clip WHERE cate_id = ? ";
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, cateId);
			
			rs = psmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception e) {
			logger.error("countByCateId: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return result;
	}
	
	@SuppressWarnings("unchecked")
	public Vector findAll(int currPage, int rowsPerPage) {
		int startRows = (currPage - 1)*rowsPerPage;
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		Clip clip = null;
		Vector vClip = null;
		
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT id, name, name_vn, clip_length, clip_capacity, cate_id, content, image, status, gen_date, last_updated, download_counter" +
			" FROM clip " +
			" ORDER BY gen_date DESC, download_counter DESC " +
			" LIMIT ? , ? ";
			psmt = conn.prepareStatement(strSQL);
			psmt.setInt(1, startRows);
			psmt.setInt(2, rowsPerPage);
			
			rs = psmt.executeQuery();
			vClip = new Vector();
			while (rs.next()) {
				clip = new Clip();
				clip.setId(rs.getBigDecimal(1));
				clip.setName(rs.getString(2));
				clip.setNameVn(rs.getString(3));
				clip.setClipLength(rs.getString(4));
				clip.setClipCapacity(rs.getInt(5));
				clip.setCateId(rs.getBigDecimal(6));
				clip.setUrlContent(rs.getString(7));
				clip.setUrlImage(rs.getString(8));
				clip.setStatus(rs.getInt(9));
				clip.setGenDate(rs.getTimestamp(10));
				clip.setLastUpdated(rs.getTimestamp(11));
				clip.setDownloadCounter(rs.getInt(12));
				
				vClip.addElement(clip);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
			System.out.println("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return vClip;
	}
	
	@SuppressWarnings("unchecked")
	public Vector findByCateId(BigDecimal cateId, int currPage, int rowsPerPage) {
		int startRows = (currPage - 1)*rowsPerPage;
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		Clip clip = null;
		Vector vClip = null;
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT id, name, name_vn, clip_length, clip_capacity, cate_id, content, image, status, gen_date, last_updated, download_counter" +
			" FROM clip WHERE cate_id = ? " +
			" ORDER BY gen_date DESC, download_counter DESC " +
			" LIMIT ? , ? ";
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, cateId);
			psmt.setInt(2, startRows);
			psmt.setInt(3, rowsPerPage);
			rs = psmt.executeQuery();
			vClip = new Vector();
			while (rs.next()) {
				clip = new Clip();
				clip.setId(rs.getBigDecimal(1));
				clip.setName(rs.getString(2));
				clip.setNameVn(rs.getString(3));
				clip.setClipLength(rs.getString(4));
				clip.setClipCapacity(rs.getInt(5));
				clip.setCateId(rs.getBigDecimal(6));
				clip.setUrlContent(rs.getString(7));
				clip.setUrlImage(rs.getString(8));
				clip.setStatus(rs.getInt(9));
				clip.setGenDate(rs.getTimestamp(10));
				clip.setLastUpdated(rs.getTimestamp(11));
				clip.setDownloadCounter(rs.getInt(12));
				
				vClip.addElement(clip);
			}
		} catch (Exception e) {
			logger.error("findByCateId: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return vClip;
	}
	
	public boolean updateRow(BigDecimal id, String name, BigDecimal cateId, int status, String clipLength) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		boolean result = false;
		try {
			strSQL = " UPDATE clip SET name = ?, name_vn = ?, cate_id = ?, status = ?, clip_length = ?, last_updated = NOW() " +
			" WHERE id = ? ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setString(1, name);
			psmt.setString(2, UTF8Tool.coDau2KoDau(name));
			psmt.setBigDecimal(3, cateId);
			psmt.setInt(4, status);
			psmt.setString(5, clipLength);
			psmt.setBigDecimal(6, id);
			
			if(psmt.executeUpdate() > 0){
				result = true;
			}
			
		} catch (Exception e) {
			logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt );
		}
		return result;
	}
	
	public boolean deleteRow(BigDecimal id) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		boolean result = false;
		try {
			strSQL = " DELETE FROM clip WHERE id = ? ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, id);
			
			if(psmt.executeUpdate() > 0){
				result = true;
			}
			
		} catch (Exception e) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt );
		}
		return result;
	}
	
	public boolean updateImageFile(BigDecimal id, String fileName){
		if(id == null){
			logger.log("update parameter is null");
			return false;
		}
		boolean result = false;
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSQL = null;
		ResultSet rs = null;
		
		try {
			conn = dbPool.getConnection();
			
			strSQL = "UPDATE clip SET image = ? WHERE id = ?";
			
			preStmt = conn.prepareStatement(strSQL.toString());
			preStmt.setString(1, fileName);
			preStmt.setBigDecimal(2, id);
			
			if (preStmt.executeUpdate() > 0) {
				result = true;
			}
		}catch (Exception e) {
			logger.error("updateImageFile: Error executing >>>" + e.toString());
		}finally {
			dbPool.releaseConnection(conn, preStmt, rs);
		}
		return result;
	}
	
	public boolean updateContentFile(BigDecimal id, String urlFile, int clipCapacity){
		if(id == null){
			logger.log("update parameter is null");
			return false;
		}
		boolean result = false;
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSQL = null;
		ResultSet rs = null;
		
		try {
			conn = dbPool.getConnection();
			
			strSQL = "UPDATE clip SET content = ?, clip_capacity = ? WHERE id = ?";
			
			preStmt = conn.prepareStatement(strSQL.toString());
			preStmt.setString(1, urlFile);
			preStmt.setInt(2, clipCapacity);
			preStmt.setBigDecimal(3, id);
			
			if (preStmt.executeUpdate() > 0) {
				result = true;
			}
		}catch (Exception e) {
			logger.error("updateContentFile: Error executing >>>" + e.toString());
		}finally {
			dbPool.releaseConnection(conn, preStmt, rs);
		}
		return result;
	}
}
